﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using SWENAssignment.HuiYi;
using SWENAssignment.Sophia;
using SWENAssignment.Sophia.Classes;
using SWENAssignment.Classes;

namespace SWENAssignment
{
    public class DBmanager
    {
        // ThinHui (Housekeeping)

        public static int updateStaffProfile(staffDetails s)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE dbo.staffDetails SET staffId=@staffId, contactNumber=@contactNumber,Address=@Address,DutyType=@DutyType where staffId=@staffId";
                comm.Parameters.AddWithValue("@staffId", s.StaffId);
                comm.Parameters.AddWithValue("@contactNumber", s.ContactNumber);
                comm.Parameters.AddWithValue("@Address", s.address);
                comm.Parameters.AddWithValue("@DutyType", s.dutyType);
                rowsupdated = comm.ExecuteNonQuery();
                //conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int deleteStaffProfile(string staffId)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM  dbo.staffDetails WHERE staffId = @staffId";
                comm.Parameters.AddWithValue("@staffId", staffId);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            conn.Close();
            return rowsdeleted;
        }

        public static staffDetails getByStaffId(string staffId)
        {
            staffDetails s = new staffDetails();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.staffDetails WHERE staffId = @staffId";
                comm.Parameters.AddWithValue("@staffId", staffId);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s.StaffId = (string)dr["staffId"];
                    s.lastName = (string)dr["LastName"];
                    s.firstName = (string)dr["FirstName"];
                    s.dutyType = (string)dr["DutyType"];
                    s.dob = (DateTime)dr["DOB"];
                    s.ContactNumber = (int)dr["contactNumber"];
                    s.address = (string)dr["Address"];
                    s.email = (string)dr["Email"];
                    s.gender = (string)dr["Gender"];
                    s.nric = (string)dr["NRIC"];
                    s.bankAccType = (string)dr["BankAccType"];
                    s.bankAccNumber = (string)dr["BankAccNumber"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;
        }

        public static int createStaffProfile(staffDetails s)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO dbo.staffDetails(staffId,FirstName,LastName,DutyType,DOB,contactNumber,Address,Email,Gender,NRIC,BankAccType,BankAccNumber)" +
                    " VALUES (@staffId,@FirstName,@LastName,@DutyType,@DOB,@contactNumber,@Address,@Email,@Gender,@NRIC,@BankAccType,@BankAccNumber)";
                comm.Parameters.AddWithValue("@staffId", s.StaffId);
                comm.Parameters.AddWithValue("@FirstName", s.firstName);
                comm.Parameters.AddWithValue("@LastName", s.lastName);
                comm.Parameters.AddWithValue("@DutyType", s.dutyType);
                comm.Parameters.AddWithValue("@DOB", s.dob);
                comm.Parameters.AddWithValue("@contactNumber", s.ContactNumber);
                comm.Parameters.AddWithValue("@Address", s.address);
                comm.Parameters.AddWithValue("@Email", s.email);
                comm.Parameters.AddWithValue("@Gender", s.gender);
                comm.Parameters.AddWithValue("@NRIC", s.nric);
                comm.Parameters.AddWithValue("@BankAccType", s.bankAccType);
                comm.Parameters.AddWithValue("@BankAccNumber", s.bankAccNumber);
                rowsinserted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int CheckStaffExistbyStffId(string staffId)
        {
            int result = -1;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.staffDetails WHERE staffId=@staffId";
                comm.Parameters.AddWithValue("@staffId", staffId);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = 1;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static bool Login(string loginID, string password)
        {
            bool successful = false;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.Parameters.AddWithValue("@loginID", loginID);
                comm.Parameters.AddWithValue("@password", password);
                comm.CommandText = "SELECT COUNT(*) FROM dbo.administrator WHERE loginID=@loginID AND password=@password";

                int result = (int) comm.ExecuteScalar();

                if (result == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }

                //dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return successful;
        }


        //Huiyi

        public static int InsertUserAccount(userAccount u)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into dbo.userAccount (userType, loginID, password, confirmedPassword, lastName, firstName, dateOfBirth, contNum, mailAdd, email, gender, bankAccType, bankAccNum ) values (@userType, @loginID, @password, @confirmedPassword, @lastName, @firstName, @dateOfBirth, @contNum, @mailAdd, @email, @gender, @bankAccType, @bankAccNum)";

                comm.Parameters.AddWithValue("@userType", u.UserType);
                comm.Parameters.AddWithValue("@loginID", u.LoginID);
                comm.Parameters.AddWithValue("@password", u.Password);
                comm.Parameters.AddWithValue("@confirmedPassword", u.ConfirmedPassword);
                comm.Parameters.AddWithValue("@lastName", u.LastName);
                comm.Parameters.AddWithValue("@firstName", u.FirstName);
                comm.Parameters.AddWithValue("@dateOfBirth", u.DateOfBirth);
                comm.Parameters.AddWithValue("@contNum", u.ContNum);
                comm.Parameters.AddWithValue("@mailAdd", u.MailAdd);
                comm.Parameters.AddWithValue("@email", u.Email);
                comm.Parameters.AddWithValue("@gender", u.Gender);
                comm.Parameters.AddWithValue("@bankAccType", u.BankAccType);
                comm.Parameters.AddWithValue("@bankAccNum", u.BankAccNum);

                rowsinserted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return rowsinserted;
        }

        
    public static userAccount GetUserAccountByLoginID(string loginID)
        {
            userAccount u = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.userAccount WHERE loginID=@loginID";
                comm.Parameters.AddWithValue("@loginID", loginID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u = new userAccount();
                    u.LoginID = (string)dr["LoginID"];
                    u.UserType = (string)dr["UserType"];
		            u.Password = (string)dr["Password"];
		            u.ConfirmedPassword = (string)dr["ConfirmedPassword"];
		            u.LastName = (string)dr["LastName"];
		            u.FirstName = (string)dr["FirstName"];
		            u.DateOfBirth = (DateTime)dr["DateOfBirth"];
		            u.ContNum = (int)dr["ContNum"];
                    u.MailAdd = (string)dr["MailAdd"];
		            u.Email = (string)dr["Email"];
                    u.Gender = (string)dr["Gender"];
                    u.BankAccType = (string)dr["BankAccType"];
                    u.BankAccNum = (int)dr["BankAccNum"];
                    
                }
                return u;
            }
            catch (SqlException e)
            {
                throw e;
            }

            //return u;

        }

    public static int UpdateUserAccount(userAccount u)
    {
        int rowsupdated = 0;
        try
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();


            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Update dbo.userAccount set password=@password,confirmedPassword=@confirmedPassword, contNum=@contNum,mailAdd=@mailAdd, email=@email where loginID=@loginID";

            comm.Parameters.AddWithValue("@loginID", u.LoginID);
            comm.Parameters.AddWithValue("@password", u.Password);
            comm.Parameters.AddWithValue("@confirmedPassword", u.ConfirmedPassword);
            comm.Parameters.AddWithValue("@contNum", u.ContNum);

            comm.Parameters.AddWithValue("@mailAdd", u.MailAdd);
            comm.Parameters.AddWithValue("@email", u.Email);



            rowsupdated = comm.ExecuteNonQuery();

            conn.Close();

        }
        catch (SqlException ex)
        {
            throw ex;
        }
        return rowsupdated;
    }

    public static int DeleteUserAccount(userAccount u)
       {
        int rowsdeleted = 0;
        try
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Delete from dbo.userAccount where loginID=@loginID";
            comm.Parameters.AddWithValue("@loginID", u.LoginID);
            rowsdeleted = comm.ExecuteNonQuery();

            conn.Close();

        }
        catch (SqlException ex)
        {
            throw ex;
        }
        return rowsdeleted;

    }



    public static int CheckUserAccByLoginID(string loginID)
    {
        int result = -1;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM dbo.userAccount WHERE loginID=@loginID";
            comm.Parameters.AddWithValue("@loginID", loginID);
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                result = 1;
            }
            conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return result;
    }

        // sophia

    public static int insertCheckInDetails(checkInDetails c)
    {
        int rowsinserted = 0;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "INSERT INTO dbo.checkInDetails(checkInID, customerID, reservationID, numOfAdults, numOfChildren, checkInDate, checkOutDate, roomNum, paymentType, checkInStatus)" +
            "  VALUES (@checkInID, @customerID, @reservationID, @numOfAdults, @numOfChildren, @checkInDate, @checkOutDate, @roomNum, @paymentType, @checkInStatus)";
            comm.Parameters.AddWithValue("@checkInID", c.CheckInID);
            comm.Parameters.AddWithValue("@customerID", c.CustomerID);
            comm.Parameters.AddWithValue("@reservationID", c.ReservationID);
            comm.Parameters.AddWithValue("@numOfAdults", c.NumOfAdults);
            comm.Parameters.AddWithValue("@numOfChildren", c.NumOfChildren);
            comm.Parameters.AddWithValue("@checkInDate", c.CheckInDate);
            comm.Parameters.AddWithValue("@checkOutDate", c.CheckOutDate);
            comm.Parameters.AddWithValue("@roomNum", c.RoomNum);
            comm.Parameters.AddWithValue("@paymentType", c.PaymentType);
            comm.Parameters.AddWithValue("@checkInStatus", c.CheckInStatus);
            rowsinserted = comm.ExecuteNonQuery();

            conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rowsinserted;
    }


    //public static bool isCustomerExist(string customerID)
    //{
    //    SqlConnection conn = null;

    //    try
    //    {
    //        string custID;
    //        conn = new SqlConnection();
    //        conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
    //        conn.Open();
    //        SqlCommand comm = new SqlCommand();
    //        comm.Connection = conn;
    //        comm.CommandText = "SELECT customerID FROM customersDetails WHERE customerID = @customerID";
    //        comm.Parameters.AddWithValue("@customerID", customerID);
    //        SqlDataReader dr = comm.ExecuteReader();
    //        if (dr.Read())
    //        {
    //            custID = (string)dr["CustomerID"];
    //            customerID = custID;
    //        }


    //        conn.Close();

    //        if (customerID != null)
    //        {
    //            return false;
    //        }
    //        else
    //        {
    //            return true;
    //        }
    //    }
    //    catch (SqlException e)
    //    {
    //        throw e;
    //    }

    //}

       public static int checkCustomerExistByCustomerID (string customerID)
       {
           int result = -1;
           SqlConnection conn = null;
            try
            {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = " SELECT * FROM dbo.customersDetails WHERE customerID=@customerID";
            comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = 1;
                }
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
       }



    public static int insertCustomerDetails(customersDetails cd)
    {
        int rowsinserted = 0;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = " INSERT INTO dbo.customersDetails(customerID, lastName, firstName, nationality, contactNo, address, email)" +
                " VALUES (@customerID, @lastName, @firstName, @nationality, @contactNo, @address, @email)";
            comm.Parameters.AddWithValue("@customerID", cd.CustomerID);
            comm.Parameters.AddWithValue("@lastName", cd.LastName);
            comm.Parameters.AddWithValue("@firstName", cd.FirstName);
            comm.Parameters.AddWithValue("@nationality", cd.Nationality);
            comm.Parameters.AddWithValue("@contactNo", cd.ContactNo);
            comm.Parameters.AddWithValue("@address", cd.Address);
            comm.Parameters.AddWithValue("@email", cd.Email);
            rowsinserted = comm.ExecuteNonQuery();
            conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rowsinserted;

    }



    public static int updateCustomersDetails(customersDetails cd1)
    {
        int rowsupdated = 0;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE dbo.customersDetails SET contactNo=@contactNo, address=@address, email=@email WHERE customerID=@customerID";
            comm.Parameters.AddWithValue("@customerID", cd1.CustomerID);
            comm.Parameters.AddWithValue("@contactNo", cd1.ContactNo);
            comm.Parameters.AddWithValue("@address", cd1.Address);
            comm.Parameters.AddWithValue("@email", cd1.Email);
            rowsupdated = comm.ExecuteNonQuery();
            //conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rowsupdated;
    }

    public static int insertRoomReservation(roomReservation r)
    {
        int rowsinserted = 0;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = " INSERT INTO dbo.roomReservation(reservationID, customerID, numOfAdults, numOfChildren, preferredRoomType, preferredBedType, smokingOrNot, roomNum, reservationStatus)" +
                " VALUES (@reservationID, @customerID, @numOfAdults, @numOfChildren, @preferredRoomType, @preferredBedType, @smokingOrNot, @roomNum, @reservationStatus)";
            comm.Parameters.AddWithValue("@reservationID", r.ReservationID);
            comm.Parameters.AddWithValue("@customerID", r.CustomerID);
            comm.Parameters.AddWithValue("@numOfAdults", r.NumOfAdults);
            comm.Parameters.AddWithValue("@numOfChildren", r.NumOfChildren);
            comm.Parameters.AddWithValue("@preferredRoomType", r.PreferredRoomType);
            comm.Parameters.AddWithValue("@preferredBedType", r.PreferredBedType);
            comm.Parameters.AddWithValue("@smokingOrNot", r.SmokingOrNot);
            comm.Parameters.AddWithValue("@roomNum", r.RoomNum);
            comm.Parameters.AddWithValue("@reservationStatus", r.ReservationStatus);

            rowsinserted = comm.ExecuteNonQuery();

            conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rowsinserted;
    }

    public static int updateRoomReservation(roomReservation r1)
    {
        int rowsupdated = 0;
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE dbo.roomReservation SET numOfAdults=@numOfAdults, numOfChildren=@numOfChildren, preferredRoomType=@preferredRoomType, preferredBedType=@preferredBedType WHERE reservationID=@reservationID";
            comm.Parameters.AddWithValue("@reservationID", r1.ReservationID);
            comm.Parameters.AddWithValue("@numOfAdults", r1.NumOfAdults);
            comm.Parameters.AddWithValue("@numOfChildren", r1.NumOfChildren);
            comm.Parameters.AddWithValue("preferredRoomType", r1.PreferredRoomType);
            comm.Parameters.AddWithValue("preferredBedType", r1.PreferredBedType);
            rowsupdated = comm.ExecuteNonQuery();
            //conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rowsupdated;
    }
    public static int deleteReservation(string reservationID)
    {
        int rowsdeleted = 0;
        try
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "DELETE FROM dbo.roomReservation WHERE reservationID=@reservationID";
            comm.Parameters.AddWithValue("@reservationID", reservationID);
            rowsdeleted = comm.ExecuteNonQuery();
            conn.Close();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        return rowsdeleted;
    }

        public static customersDetails getByCustomerID (string customerID)
        {
            customersDetails cd1 = new customersDetails();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.customersDetails WHERE customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    cd1.CustomerID = (string)dr["customerID"];
                    cd1.LastName = (string)dr["lastName"];
                    cd1.FirstName = (string)dr["firstName"];
                    cd1.Nationality = (string)dr["nationality"];
                    cd1.ContactNo = (int)dr["contactNo"];
                    cd1.Address = (string)dr["address"];
                    cd1.Email = (string)dr["email"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cd1;
        }


        public static bool ReservationIDIsExist(string reservationID)
        {
            string result;
            Boolean a = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.roomReservation WHERE reservationID=@reservationID";
                comm.Parameters.AddWithValue("@reservationID", reservationID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = (string)dr["reservationID"];
                    if (result == reservationID)
                    {
                        a = true;
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return a;
        }


        public static bool checkInIDIsExist(string checkInID)
        {
            string result;
            Boolean a = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.checkInDetails WHERE checkInID=@checkInID";
                comm.Parameters.AddWithValue("@checkInID", checkInID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = (string)dr["checkInID"];
                    if (result == checkInID)
                    {
                        a = true;
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return a;
        }





        public static roomReservation getRoomReservationByID(string reservationID)
        {
            roomReservation rr = new roomReservation();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM dbo.roomReservation WHERE reservationID=@reservationID";
                comm.Parameters.AddWithValue("@reservationID", reservationID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    rr.ReservationID = (string)dr["reservationID"];
                    rr.CustomerID = (string)dr["customerID"];
                    rr.NumOfAdults = (int)dr["numOfAdults"];
                    rr.NumOfChildren = (int)dr["numOfChildren"];
                    rr.PreferredRoomType = (string)dr["preferredRoomType"];
                    rr.PreferredBedType = (string)dr["preferredBedType"];
                    rr.SmokingOrNot = (string)dr["smokingOrNot"];
                    rr.ReservationStatus = (string)dr["reservationStatus"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rr;
        }

       
        public static int UpdateRoomStatus(string roomNum, string updateStatus, string reservationID)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {

                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE dbo.rooms SET roomStatus=@updateStatus, reservationID=@reservationID where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@updateStatus", updateStatus);
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                comm.Parameters.AddWithValue("@reservationID", reservationID);
                rowsupdated = comm.ExecuteNonQuery();
                //conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }


        public static int UpdateCheckInStatus(string reservationID, string status)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {

                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE dbo.roomReservation SET reservationStatus=@checkInStatus where reservationID=@reservationID";
                comm.Parameters.AddWithValue("@checkInStatus", status);
                comm.Parameters.AddWithValue("@reservationID", reservationID);
                
                rowsupdated = comm.ExecuteNonQuery();
                //conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }


    //    //dennis

    public static Boolean LogIn(string loginID, string password)
    {
        SqlConnection conn = null;
        Boolean b = false;
        LogIn lon = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM userAccount WHERE loginID = @loginID AND password = @password";
            comm.Parameters.AddWithValue("@loginID", loginID);
            comm.Parameters.AddWithValue("@password", password);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                lon = new LogIn();
                lon.LoginID = (string)dr["loginID"];
                lon.Password = (string)dr["password"];
                if (lon != null)
                {
                    b = true;
                }
            }
            dr.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return b;
    }

    public static CheckInPage GetDataByID(string id)
    {
        SqlConnection conn = null;
        CheckInPage c = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM checkInDetails WHERE checkInID = @checkInID";
            comm.Parameters.AddWithValue("@checkInID", id);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                c = new CheckInPage();
                c.RoomLevel = (int)dr["roomLevel"];
                c.RoomNum = (string)dr["roomNum"];
                c.RoomCategory = (string)dr["roomCategory"];
                c.CheckInDate = (DateTime)dr["checkInDate"];
                c.CheckOutDate = (DateTime)dr["checkOutDate"];
                c.NumOfRooms = (int)dr["numOfRooms"];
                c.Price = (decimal)dr["price"];
            }
            dr.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return c;
    }

    public static int CreatePayment(Payment p)
    {
        int rows = 0;

        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "INSERT INTO Payment(lastName, firstName, totalPrice, creditCardNum, paymentType, expiryDate)" +
            " VALUES (@lastName, @firstName, @totalPrice, @creditCardNum, @paymentType, @expiryDate)";

            comm.Parameters.AddWithValue("@lastName", p.LastName);
            comm.Parameters.AddWithValue("@firstName", p.FirstName);
            comm.Parameters.AddWithValue("@totalPrice", p.TotalPrice);
            comm.Parameters.AddWithValue("@creditCardNum", p.CreditCardNum);
            comm.Parameters.AddWithValue("@paymentType", p.PaymentType);
            comm.Parameters.AddWithValue("@expiryDate", p.ExpiryDate);

            rows = comm.ExecuteNonQuery();
            conn.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }
        return rows;
    }

    public static GuestDetails GetDetailsByRoomNumber(string num)
    {
        SqlConnection conn = null;
        GuestDetails g = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM customerDetails WHERE roomNum = @roomNum";
            comm.Parameters.AddWithValue("@roomNum", num);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                g = new GuestDetails();
                g.LastName = (string)dr["lastName"];
                g.FirstName = (string)dr["firstName"];
                g.NumOfAdults = (int)dr["numOfAdults"];
                g.NumOfChildren = (int)dr["numOfChildren"];
                g.ContactNo = (int)dr["contNum"];
                g.Address = (string)dr["mailAdd"];
                g.Email = (string)dr["email"];
            }
            dr.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return g;
    }

    public static Room AllRooms()
    {
        SqlConnection conn = null;
        Room r = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM rooms";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                r = new Room();
                r.RoomNum = (string)dr["roomNum"];
                r.RoomStatus = (string)dr["roomStatus"];
                r.RoomType = (string)dr["roomType"];
                r.RoomPrice = (decimal)dr["roomPrice"];
            }
            dr.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return r;
    }

    public static GuestByDate GetDetailsByDate(string date)
    {
        SqlConnection conn = null;
        GuestByDate gg = null;
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["HotelDBConnectionString"].ConnectionString;
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM customerDetails WHERE checkInDate = @checkInDate AND checkOutDate = @checkOutDate";
            comm.Parameters.AddWithValue("@checkInDate", date);
            comm.Parameters.AddWithValue("@checkOutDate", date);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gg = new GuestByDate();
                gg.LastName = (string)dr["lastName"];
                gg.FirstName = (string)dr["firstName"];
                gg.NumOfAdults = (int)dr["numOfAdults"];
                gg.NumOfChildren = (int)dr["numOfChildren"];
                gg.ContNum = (int)dr["contNum"];
                gg.MailAdd = (string)dr["mailAdd"];
                gg.Email = (string)dr["email"];
            }
            dr.Close();
        }
        catch (SqlException e)
        {
            throw e;
        }

        return gg;
    }
    }
}
